IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_MediaContentFindByKeywords]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_MediaContentFindByKeywords]
GO
create procedure [dbo].[cms_sp_MediaContentFindByKeywords] 
 (@SearchQuery varchar(max), 
  @IsInAdministrationArea bit = null, 
  @SiteGroupId int = null, 
  @SiteId int = null, 
  @MediaId int = null 
 ) 
 AS 
  
 declare @Searched table  
 ( 
 	MediaId int not null	 
 ); 
  
 WITH  tree 
         AS ( 
             SELECT  MediaId, ParentMediaId 
             FROM    Media 
             WHERE   MediaId = @MediaId or (@MediaId is null and ParentMediaId IS NULL)  
             UNION ALL 
               --recursive member 
             SELECT  t.MediaId, t.ParentMediaId 
             FROM    Media t 
                     JOIN tree as a 
                       ON t.ParentMediaId = a.MediaId 
            ) 
 insert into @Searched (MediaId) 
  select MediaId FROM tree 
  
 select M.[MediaContentId] as [Id] 
       ,M.[MediaId] 
       ,M.[SiteId] 
       ,M.[SiteGroupId] 
       ,M.[MetaCode] 
       ,M.[Description] 
       ,M.[CreatedBy] 
       ,M.[DateCreated] 
       ,M.[LastUpdatedBy] 
       ,M.[DateLastUpdated] 
       ,[MediaContentName] 
 	  ,MediaFiles.MediaFileId as MainFile_Id 
 	  ,IsPrimary as MainFile_IsPrimary 
 	  ,MimeTypes.MimeTypeId as MainFile_MimeTypeId 
 	  ,MimeTypes.MimeTypeId as MainFile_MimeType_Id 
 	  ,ContentType as MainFile_MimeType_ContentType 
 	  ,MimeTypeName as MainFile_MimeType_MimeTypeName 
 	  ,IsImage as MainFile_MimeType_IsImage 
 	  ,LinkedFileName as MainFile_LinkedFileName 
 	  ,OriginalFileName as MainFile_OriginalFileName 
 	  ,FileSize as MainFile_FileSize 
 	  ,Height as MainFile_Height 
 	  ,Width as MainFile_Width 
   from [dbo].MediaContents M join @Searched s on M.MediaId = S.MediaId  
        join dbo.MediaContentFileLinks 
        on MediaContentFileLinks.MediaContentId = M.MediaContentId and IsPrimary=1 
 	   join dbo.MediaFiles on MediaContentFileLinks.MediaFileId = MediaFiles.MediaFileId 
 	   left join dbo.MimeTypes 
 		on MimeTypes.MimeTypeId = MediaFiles.MimeTypeId 
 	 WHERE ((M.SiteGroupId is null and M.SiteId is null) or  
 		(M.SiteGroupId = @SiteGroupId and M.SiteId is null) or 
 		(M.SiteGroupId = @SiteGroupId and M.SiteId = @SiteId)) and  
 		M.MediaContentName like '%'+@searchQuery+'%'
GO

